require 'apr.uri'
require 'apr.db.raw'
require 'apr.oo'
local apr, tostring, type, setmetatable, pairs, table =
      apr, tostring, type, setmetatable, pairs, table

--- apr.db defines the helper objects and libraries the wrap around the apr.db.raw APIs
module 'apr.db'

--- Create a new database connection object
-- @param type the apr_dbd driver string
-- @param raw the raw database connection
-- @return the new database connection
Connection = apr.oo.Class(function(self, type, raw)
    self.driver = type; self.raw = raw; self.connected = true
end)

--- Close this connection and free up resources
function Connection:close()
    if self.connected then
        apr.db.raw.disconnect(self.raw)
        self.connected = false
        self.raw = nil
    end
end

--- Check if the database is currently connected and return a true/false value
-- @return true if the database is currently connected and available.  False otherwise
function Connection:check()
    if self.connected then
        return apr.db.raw.check(self.raw)
    else
        return false
    end
end

--- Execute a non-select SQL statement, returning the number of affected rows
-- @param sql the SQL statement
-- @param ... all extra parameters will be bound into the query
-- @return the number of affected rows
function Connection:exec(sql, ...)
    local bind = {...}
    if bind ~= nil then
        sql = self:escapequery(sql, bind)
    end
    
    return apr.db.raw.exec(self.raw, sql)
end

--- Execute a select SQL statement, returning the results
-- @param sql the SQL statement
-- @param ... all extra parameters will be bound into the query
-- @return the apr.db.Result object for the results of this query
function Connection:select(sql, ...)
    local bind = {...}
    if bind ~= nil then
        sql = self:escapequery(sql, bind)
    end
    
    return Result.new(self, apr.db.raw.select(self.raw, sql))
end

--- Replace the ? characters in a query with the escaped forms of the values in the bind table
-- @param sql the SQL statement with question marks where the bound parameters should be interpolated
-- @param bind the parameters to bind into the query
-- @return the SQL statement with all bound parameters escaped and included in the query
function Connection:escapequery(sql, bind)
    local n = 0
    return sql:gsub('?', function(mark)
        n = n + 1
        return self:escape(bind[n])
    end, #bind)
end

--- Escape an individual parameter for use in an SQL query
-- @param param the parameter to escape - should be a string, number, or nil
-- @return the parameter value as a properly escaped string to be placed in an SQL statement
function Connection:escape(param)
    if type(param) == 'nil' then return 'NULL' end
    
    local escaped = apr.db.raw.escape(self.raw, tostring(param));
    if type(param) == 'string' then
        return '"' .. escaped .. '"'
    end
    return escaped
end

--- Helper function for inserting records into a table
-- @param tname the name of the table to insert into
-- @param values the table of key = value pairs for the fields to insert
-- @return the number of affected rows from inserting the record (should be 1)
function Connection:insert(tname, values)
    local fields = {}
    local placeholders = {}
    local bind = {}
    for key, val in pairs(values) do
        table.insert(fields, key)
        table.insert(placeholders, '?')
        table.insert(bind, val)
    end
    
    local sql = self:escapequery('INSERT INTO ' .. tname .. ' (' .. table.concat(fields, ',') .. ') VALUES (' .. table.concat(placeholders, ',') .. ')', bind)
    return apr.db.raw.exec(self.raw, sql)
end

--- Helper function for updating records into a table
-- @param tname the name of the table to update
-- @param values the key = value pairs to update
-- @param where the where clause of the query.  This can be a table of field = value pairs
-- where all must match, or an SQL string with optional question marks for bound parameters
-- @param ... all other parameters will be treated as bound parameter values for the where
-- part of the query
-- @return the number of affected rows
function Connection:update(tname, values, where, ...)
    local bind = {...}
    local updatevals = {}
    for key, val in pairs(values) do
        table.insert(updatevals, key .. '=' .. self:escape(val))
    end
    
    local sql = 'UPDATE ' .. tname .. ' SET ' .. table.concat(updatevals, ',')
    if type(where) == 'table' then
        local wherevals = {}
        for key, val in pairs(where) do
            table.insert(wherevals, key .. '=' .. self:escape(val))
        end
        sql = sql .. ' WHERE ' .. table.concat(wherevals, ' AND ')
    elseif type(where) == 'string' then
        if bind ~= nil then
            where = self:escapequery(where, bind)
        end
        
        sql = sql .. ' WHERE ' .. where
    end
    return apr.db.raw.exec(self.raw, sql)
end

--- Prepare an SQL statement for re-use
-- @param sql the SQL statement to prepare
-- @return the apr.db.Statement object for re-use
function Connection:prepare(sql)
    return Statement.new(self, sql)
end

--- Create a new result object representing the results of an SQL select statement
-- @param conn the apr.db.Connection object that generated this result
-- @param raw the raw apr_dbd result
-- @return the resulting apr.db.Result object table
Result = apr.oo.Class(function(self, conn, raw)
    self.conn = conn; self.raw = raw; self.numrows = apr.db.raw.numrows(raw); self.numcols = apr.db.raw.numcols(raw)
end)

--- Iterate over the rows with keys by field names
-- @return the iterating closure
function Result:rows()
    return apr.db.raw.iterrows(self.raw, true)
end

--- Iterate over the rows with numeric keys - first column has the key "1", second has "2" and so forth
-- @return the iterating closure
function Result:irows()
    return apr.db.raw.iterrows(self.raw, false)
end

--- Close the result, clearing the raw object and memory - this will be called automatically during GC
function Result:close()
    apr.db.raw.freeresult(self.raw)
    self.raw = nil
end

--- Create a new prepared statement object
-- @param conn the apr.db.Connection object that prepared the statement
-- @param sql the SQL statement to prepare
-- @return the newly generated apr.db.Statement object
Statement = apr.oo.Class(function(self, conn, sql)
    self.conn = conn; self.raw = nil; self.query = sql
end)

--- Execute a prepared statement with bound parameters and return the number of affected rows
-- @param ... all parameters will be bound to the prepared statement
-- @return the number of affected rows
function Statement:exec(...)
    local bind = {...}
    self:prepareraw(bind)
    return apr.db.raw.pexec(self.raw, bind)
end

--- Execute a prepared statement with bound parameters and return the resultset
-- @param ... all parameters will be bound to the prepared statement
-- @return the apr.db.Result object for the results of the select statement
function Statement:select(...)
    local bind = {...}
    self:prepareraw(bind)
    return Result.new(self.conn, apr.db.raw.pselect(self.raw, bind))
end

--- The first time a prepared statement is executed, really create the prepared statement
-- Do this because apr_dbd uses the type information in the prepared query itself
-- @param bind the parameters to use for introspecting the type information
function Statement:prepareraw(bind)
    if self.raw == nil then
        local n = 0
        local rawquery = self.query:gsub('?', function(mark)
            n = n + 1
            local bindtype = type(bind[n])
            
            if bindtype == 'number' then
                return '%lf'
            elseif bindtype == 'boolean' then
                return '%d'
            else
                return '%s'
            end
        end, #bind)
        self.raw = apr.db.raw.prepare(self.conn.raw, rawquery, #bind)
    end
end

--- Factory function to create a database connection based on the provided DSN
-- @param dsn the DSN string
-- @return the apr.db.Connection object for the DSN
function connect(dsn)
    dsn = apr.uri.parse(dsn)
    local conn = nil
    if dsn.scheme == 'sqlite' or dsn.scheme == 'sqlite3' then
        if not dsn.path then
            conn = Connection.new('sqlite3', apr.db.raw.connect('sqlite3', ':memory:'))
        else
            conn = Connection.new('sqlite3', apr.db.raw.connect('sqlite3', dsn.path))
        end
    elseif dsn.scheme == 'sqlite2' then
        local conn = Connection.new('sqlite2', apr.db.raw.connect('sqlite2', dsn.path))
    elseif dsn.scheme == 'postgres' or dsn.scheme == 'postgresql' or dsn.scheme == 'pgsql' then
        
    elseif dsn.scheme == 'mysql' then
    elseif dsn.scheme == 'oracle' then
    elseif dsn.scheme == 'odbc' then
    elseif dsn.scheme == 'freetds' then
    else
        error("Unknown database type or invalid DSN: " .. dsn.scheme)
    end
    
    return conn
end
